/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package by.vdl.websort.server.dao.mysql;

import by.vdl.websort.server.dao.BaseDao;

/**
 *
 * @author zeor
 */
public class StudyDaoImpl extends BaseDao /*implements StudyDao*/{
    
   /* private final StudyRowMapper studyRowMapper = new StudyRowMapper();
    private final CategoryRowMapper categoryRowMapper = new CategoryRowMapper();
    private final ItemRowMapper itemRowMapper = new ItemRowMapper();
    private final UnsortedStudyResultSetExtractor unsortedStudyResultSetExtractor = new UnsortedStudyResultSetExtractor();
    private final SortedTableRowMapper sortedTableRowMapper = new SortedTableRowMapper();
    
    private DataSource dataSource;

    public DataSource getDataSource() {
        return dataSource;
        
    }

    public void setDataSource(DataSource dataSource) {
        this.dataSource = dataSource;
    }

    @Transactional
    public long addStudy(String study, long userId) throws Exception {
        Map<String, ?> map = getParameters(
                "study",study,
                "userId", userId);
        String sql = "insert into `study` (name, user_id) values (:study, :userId)";
        jdbcTemplate.update(sql, map);
        return -1;
    }

    @Transactional
    public long addCategory(String category, long studyId) throws Exception {
        Map<String, ?> map = getParameters(
                "category",category,
                "studyId", studyId);
        String sql = "insert into `category` (name, study_id) values (:category, :studyId)";
        jdbcTemplate.update(sql, map);
        return -1;
    }

    @Transactional
    public long addItem(String item, long studyId) throws Exception {
        Map<String, ?> map = getParameters(
                "item",item,
                "studyId", studyId);
        String sql = "insert into `items` (name, study_id) values (:item, :studyId)";
        jdbcTemplate.update(sql, map);
        return -1;
    }

    public StudyDTO getStudyByUserPassedId(long userPassedId) throws Exception {
        Map<String, ?> map = getParameters(
                "userPassedId",userPassedId);
        String sql = "select * from `study` as study, `user_pass_study` as ups where ups.id=:userPassedId and study.id=ups.study_id";
        return jdbcTemplate.queryForObject(sql, map, studyRowMapper);
       
    }

    public StudyDTO getStudyById(long studyId) throws Exception {
        Map<String, ?> map = getParameters(
                "studyId",studyId);
        String sql = "select * from `study` where id=:studyId";
        return jdbcTemplate.queryForObject(sql, map, studyRowMapper);
    }

    public StudyUnsortedModel getUncompleatedStudy(long studyId) throws Exception {
      Map<String, ?> map = getParameters(
                "studyId",studyId);
        String sql = "select s.id as studyId, s.name as studyName, s.user_id as userId, c.id as categoryId," +
" c.name as categoryName, i.id as itemId, i.name as itemName from study as  s, category as c," +
" items as i where s.id =:studyId and c.study_id = s.id and i.study_id = s.id;";
        return jdbcTemplate.query(sql, map, unsortedStudyResultSetExtractor);
    }

    public List<SortedTableDTO> getSortedTablesByPassedUser(long userPassedStudyId) throws Exception {
       Map<String, ?> map = getParameters(
                "userPassedStudyId",userPassedStudyId);
        String sql = "select * from `category_item_links` where userPassedStudyId=:userPassedStudyId";
        return jdbcTemplate.query(sql, map, sortedTableRowMapper); 
    }
    
   @Transactional
   public SortedTableDTO addSortedTable(long categoryId, long itemId, long userPassedStudyId) throws Exception {
       Map<String, ?> map = getParameters(
                "categoryId",categoryId,
                "itemId",itemId,
                "userPassedStudyId",userPassedStudyId);
        String sql = "insert into `category_item_links` (category_id, item_id, user_pass_study_id) values (:categoryId, :itemId, :userPassedStudyId)";
        if(jdbcTemplate.update(sql, map)>0){
            SortedTableDTO sortedTableDTO = new SortedTableDTO();
            sortedTableDTO.setId(-1);
            sortedTableDTO.setCategoryId(categoryId);
            sortedTableDTO.setItemId(itemId);
            sortedTableDTO.setUserPassStudyId(userPassedStudyId);
            return sortedTableDTO;
        }
        return null;
    }

    public CategoryDTO getCategoryById(long categoryId) throws Exception {
        Map<String, ?> map = getParameters(
                "categoryId",categoryId);
        String sql = "select * from `category` where id=:categoryId";
        return jdbcTemplate.queryForObject(sql, map, categoryRowMapper);
        
    }

    public ItemDTO getItemById(long itemId) throws Exception {
        Map<String, ?> map = getParameters(
                "itemId",itemId);
        String sql = "select * from `items` where id=:itemId";
        //return jdbcTemplate.queryForObject(sql, map, itemRowMapper);
    }

    public List<StudyDTO> listStudy() throws Exception {
        Map<String, ?> map = getParameters();
        String sql = "select * from `study`";
        //return jdbcTemplate.query(sql, map, studyRowMapper);
    }

    public List<SortedTableDTO> getUnsortedDataSubmission(long studyId) throws Exception {
        Map<String, ?> map = getParameters(
                "study_id", studyId);
        String sql = "select * from category_item_links as cil where cil.category_id in (select c.id from category as c where c.study_id = :study_id) order by cil.user_pass_study_id";
        //return jdbcTemplate.query(sql, map, sortedTableRowMapper);
    }
    */
}
